﻿declare @date_update nvarchar(100)= (select [DATE_UPDATE] from [DATE_EM_REPORT])

IF EXISTS
(SELECT *
FROM DBO.SYSOBJECTS O
WHERE O.XTYPE IN ('U') AND O.NAME = 'LOAN_ALL_DAY')
delete from LOAN_ALL_DAY

 insert LOAN_ALL_DAY
SELECT *, null, null
FROM SERVER74.BICDATA.DBO.BALANCE_LIVE A
WHERE BUSINESS_DATE=@date_update
AND APP='LOAN'
AND ((A.SEGMENT not in ('KHCN','HH') AND A.BRANCH_CODE NOT IN (SELECT BRANCH_CODE FROM PhuongAnh.DBO.BRANCHOFF))
                        OR (A.SEGMENT IS NULL AND LEFT(A.SECTOR,1)>1))

--alter table LOAN_ALL_DAY
--add SAN_PHAM_LOAN nvarchar(100)

--alter table LOAN_ALL_DAY add NHOM_SP_LOAN nvarchar(100)

update LOAN_ALL_DAY
set SAN_PHAM_LOAN='Unsecured'
where chuong_trinh_sp like '%bil%' or chuong_trinh_sp like '%Clean%'

update LOAN_ALL_DAY
set SAN_PHAM_LOAN='CAR'
where chuong_trinh_sp like '%CAR%' and SAN_PHAM_LOAN is null

update LOAN_ALL_DAY
set SAN_PHAM_LOAN='CAR'
where  [SUB_CATEGORY] IN ('101','102','103','104','105','106') and SAN_PHAM_LOAN is null

UPDATE A
SET a.SAN_PHAM_LOAN=B.[MAP LAI (DUNG DE MAP)]
FROM GIANGLH3.RISK.DBO.MAP_SANPHAM_FINAL B,LOAN_ALL_DAY A
WHERE A.PRODUCT_CODE = B.[VPB LN PR]
AND a.SAN_PHAM_LOAN IS NULL
						


UPDATE LOAN_ALL_DAY
 SET NHOM_SP_LOAN = CASE WHEN SAN_PHAM_LOAN = 'CAR' THEN '1.CAR'
                    WHEN  SAN_PHAM_LOAN IN ('WC reducing','WC installment','WC Revolving',N'Cho vay bổ sung vốn lưu động ngắn hạn thông thường') THEN '2.WORKING_CAPITAL'
					WHEN  SAN_PHAM_LOAN = 'Project/Property Finance' THEN '3.PROJECT/PROPERTY FINANCE'
					WHEN  SAN_PHAM_LOAN = 'Unsecured' THEN '4.UNSECURED'
					WHEN  SAN_PHAM_LOAN = 'CREDIT CARD' THEN '5.CREDIT_CARD'
					WHEN  SAN_PHAM_LOAN in ('OTHER','Valuable paper') THEN '6.OTHER'
					ELSE '6.OTHER'
					END
              
delete from   TBL_CM_LOAN       

insert TBL_CM_LOAN   
SELECT a.BUSINESS_DATE,a.CIF,CUSTOMER_NAME,A.DAO AS DAO_SP,B.DAO AS DAO_OPEN_CIF,CHUONG_TRINH_SP,CT_UUDAI_VAY,NHOM_SP_LOAN,CASE WHEN CHANNEL LIKE '%DSA%' THEN 'DSA' ELSE 'OTHER' END CHANNEL,SUM(BAL_QD) AS BAL_QD , '0' as nhom_no
--into TBL_CM_LOAN
 FROM LOAN_ALL_DAY A
 LEFT JOIN TBL_CUSTOMER B
 ON A.CIF=B.CIF
 where a.BUSINESS_DATE=@date_update
 --where a.dao<>b.dao collate DATABASE_default
 GROUP BY a.BUSINESS_DATE,a.CIF,CUSTOMER_NAME,A.DAO ,CHUONG_TRINH_SP,CT_UUDAI_VAY,CASE WHEN CHANNEL LIKE '%DSA%' THEN 'DSA' ELSE 'OTHER' END,B.DAO,NHOM_SP_LOAN
 
 update TBL_CM_LOAN
 set CHUONG_TRINH_SP = 'LD-00-NONE' where CHUONG_TRINH_SP is null

 update TBL_CM_LOAN
 set CT_UUDAI_VAY = '00-Khong tham gia CT uu dai vay' where CT_UUDAI_VAY is null
 DECLARE @MONTH NVARCHAR(10) =(select MAX_MONTH from DATE_EM_REPORT)
 
 IF not EXISTS
      (SELECT *
         FROM [GIANGLH3].[RISK].DBO.SYSOBJECTS O
        WHERE O.XTYPE IN ('U') AND O.NAME = 'SAOKE_'+@MONTH+'')
        set @month= convert(nvarchar(6), dateadd(m,-1,(select MAX_DATE_ENDMONTH from DATE_EM_REPORT)),112)

 print @month
 EXEC('
  update TBL_CM_LOAN
  set nhom_no = [nhom nomax]
  FROM GIANGLH3.RISK.[DBO].[SAOKE_'+@MONTH+'] a, TBL_CM_LOAN b
   WHERE a.custid = b.CIF
   ')
